/**
 * 
 */
package idv.takeshi.software.productline.bookstore.infrastructure.persistence.jpa;

import java.math.BigDecimal;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.persistence.EntityExistsException;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.Validate;
import org.springframework.stereotype.Repository;

import idv.takeshi.software.productline.bookstore.domain.model.order.OrderItem;
import idv.takeshi.software.productline.bookstore.domain.model.order.OrderRepository;
import idv.takeshi.software.productline.bookstore.domain.model.order.Invoice;
import idv.takeshi.software.productline.bookstore.domain.model.order.Order;
import idv.takeshi.software.productline.bookstore.domain.model.order.OrderDuplicateException;
import idv.takeshi.software.productline.bookstore.domain.model.order.OrderStatus;
import idv.takeshi.software.productline.bookstore.domain.model.order.Payment;
import idv.takeshi.software.productline.bookstore.domain.model.order.PickingList;
import idv.takeshi.software.productline.bookstore.domain.model.order.PickingListItem;

/**
 * A model repository implementation for OrderRepository.
 * @author takeshi
 *
 */
@Repository("orderRepository")
public class OrderRepositoryImpl implements OrderRepository {
	
	private static final String QL_FIND_BY_IDS = "SELECT o FROM Order o WHERE o.id IN({0})";
	
	private static final String SQL_INTERSECT_STRING = "	INTERSECT ";
	
	private static final String SQL_FIND_BY_USER_ACCOUNT_NAME = 
		"	SELECT o.ID \"ID\" " +
		"	FROM USER_ACCOUNT u JOIN ROLE r ON(u.ID = r.USER_ACCOUNT_ID) JOIN CUSTOMER_ORDER o ON(r.ID = o.CUSTOMER_ID) " +
		"	WHERE UPPER(u.USER_NAME) LIKE UPPER(:accountName) ";
	
	private static final String SQL_FIND_BY_PREFIX =
		"	SELECT o.ID \"ID\" " +
		"	FROM CUSTOMER_ORDER o " +
		"	WHERE 1=1 ";
	
	private static final String SQL_FIND_BY_RECIPIENT_NAME =
		SQL_FIND_BY_PREFIX + "	AND UPPER(o.RECIPIENT_NAME) LIKE UPPER(:recipientName) ";
	
	private static final String SQL_FIND_BY_ORDER_STATUS =
		SQL_FIND_BY_PREFIX + "	AND o.STATUS = :orderStatus ";
	
	private static final String SQL_FIND_BY_UPDATE_TIME_SUFFIX_1 =
		"	AND FORMATDATETIME(o.UPDATE_TIME, 'yyyy-MM-dd') " + 
		"BETWEEN FORMATDATETIME(:fromUpdateTime, 'yyyy-MM-dd') AND " + 
		"FORMATDATETIME(:toUpdateTime, 'yyyy-MM-dd') ";
	
	private static final String SQL_FIND_BY_UPDATE_TIME_SUFFIX_2 =
		"	AND FORMATDATETIME(o.UPDATE_TIME, ''yyyy-MM-dd'') {0}= FORMATDATETIME(:updateTime, ''yyyy-MM-dd'')";
	
	private static final String SQL_FIND_BY_CREATE_TIME_SUFFIX_1 =
		"	AND FORMATDATETIME(o.CREATE_TIME, 'yyyy-MM-dd') " + 
		"BETWEEN FORMATDATETIME(:fromCreateTime, 'yyyy-MM-dd') AND " + 
		"FORMATDATETIME(:toCreateTime, 'yyyy-MM-dd') ";
	
	private static final String SQL_FIND_BY_CREATE_TIME_SUFFIX_2 =
		"	AND FORMATDATETIME(o.CREATE_TIME, ''yyyy-MM-dd'') {0}= FORMATDATETIME(:createTime, ''yyyy-MM-dd'')";
	
	private static final String SQL_FIND_BY_PRICE_SUFFIX_1 =
		"	AND o.PRICE BETWEEN :fromPrice AND :toPrice ";
	
	private static final String SQL_FIND_BY_PRICE_SUFFIX_2 =
		"	AND o.PRICE {0}= :price ";
	
	private static final String SQL_FIND_BY_CRITERIA =
		"SELECT ID " +
		"FROM ( " +
		"	{0} " +
		") ";
	
	@PersistenceContext
	private EntityManager entityManager;

	/* (non-Javadoc)
	 * @see idv.takeshi.software.productline.bookstore.domain.model.order.OrderRepository#add(idv.takeshi.software.productline.bookstore.domain.model.order.Order)
	 */
	public void add(Order order) throws OrderDuplicateException {
		Validate.notNull(order, "order shall always not be null");
		try {
			this.entityManager.persist(order);
		} catch(EntityExistsException  e){
			throw new OrderDuplicateException("order:" + order + " is duplicate");
		}
	}

	/* (non-Javadoc)
	 * @see idv.takeshi.software.productline.bookstore.domain.model.order.OrderRepository#add(idv.takeshi.software.productline.bookstore.domain.model.order.Invoice)
	 */
	public void add(Invoice invoice) {
		Validate.notNull(invoice, "invoice shall always not be null");
		this.entityManager.persist(invoice);
	}

	/* (non-Javadoc)
	 * @see idv.takeshi.software.productline.bookstore.domain.model.order.OrderRepository#add(idv.takeshi.software.productline.bookstore.domain.model.order.Payment)
	 */
	public void add(Payment payment) {
		Validate.notNull(payment, "payment shall always not be null");
		this.entityManager.persist(payment);
	}

	/* (non-Javadoc)
	 * @see idv.takeshi.software.productline.bookstore.domain.model.order.OrderRepository#findById(java.lang.Long)
	 */
	public Order findById(Long id) {
		Validate.notNull(id, "id shall always not be null");
		return this.entityManager.find(Order.class, id);
	}

	/* (non-Javadoc)
	 * @see idv.takeshi.software.productline.bookstore.domain.model.order.OrderRepository#remove(idv.takeshi.software.productline.bookstore.domain.model.order.Order)
	 */
	public void remove(Order order) {
		Validate.notNull(order, "order shall always not be null");
		this.entityManager.remove(order);
	}

	/* (non-Javadoc)
	 * @see idv.takeshi.software.productline.bookstore.domain.model.order.OrderRepository#remove(idv.takeshi.software.productline.bookstore.domain.model.order.Invoice)
	 */
	public void remove(Invoice invoice) {
		Validate.notNull(invoice, "invoice shall always not be null");
		this.entityManager.remove(invoice);
	}

	/* (non-Javadoc)
	 * @see idv.takeshi.software.productline.bookstore.domain.model.order.OrderRepository#remove(idv.takeshi.software.productline.bookstore.domain.model.order.Payment)
	 */
	public void remove(Payment payment) {
		Validate.notNull(payment, "payment shall always not be null");
		this.entityManager.remove(payment);
	}
	
	/* (non-Javadoc)
	 * @see idv.takeshi.software.productline.bookstore.domain.model.order.OrderRepository#update(idv.takeshi.software.productline.bookstore.domain.model.order.Order)
	 */
	public void update(Order order){
		Validate.notNull(order, "order shall always not be null");
		Payment payment = null;
		Invoice invoice = null;
		Set<OrderItem> orderItems = null;
		Set<PickingList> pickingLists = null;
		
		this.entityManager.merge(order);
		
		payment = order.getPayment();
		if(null != payment)
			this.entityManager.merge(payment);
		
		invoice = order.getInvoice();
		if(null != invoice)
			this.entityManager.merge(invoice);
		
		orderItems = order.getOrderItems();
		for(OrderItem item : orderItems){
			this.entityManager.merge(item);
		}
		
		pickingLists = order.getPickingLists();
		if(CollectionUtils.isNotEmpty(pickingLists)){
			for(PickingList list : pickingLists){
				this.entityManager.merge(list);
				for(PickingListItem item : list.getPickingListItems()){
					this.entityManager.merge(item);
				}
			}
		}
	}

	/* (non-Javadoc)
	 * @see idv.takeshi.software.productline.bookstore.domain.model.order.OrderRepository#find(java.lang.String, java.lang.String, idv.takeshi.software.productline.bookstore.domain.model.order.OrderStatus, java.util.Date, java.util.Date, java.util.Date, java.util.Date, java.math.BigDecimal, java.math.BigDecimal)
	 */
	@SuppressWarnings("unchecked")
	public Set<Order> find(String userName, String recipientName,
			OrderStatus orderStatus, Date fromUpdateTime, Date toUpdateTime,
			Date fromCreateTime, Date toCreateTime, BigDecimal fromPrice,
			BigDecimal toPrice) {
		
		Set<Order> orders = null;
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramsMap = new HashMap<String, Object>();
		
		if(StringUtils.isNotBlank(userName)){
			sql.append(SQL_FIND_BY_USER_ACCOUNT_NAME);
			paramsMap.put("accountName", "%" + userName + "%");
		}
		
		if(StringUtils.isNotBlank(recipientName)){
			if(sql.length() > 0)
				sql.append(SQL_INTERSECT_STRING);
			sql.append(SQL_FIND_BY_RECIPIENT_NAME);
			paramsMap.put("recipientName", "%" + recipientName + "%");
		}
		
		if(null != orderStatus){
			if(sql.length() > 0)
				sql.append(SQL_INTERSECT_STRING);
			sql.append(SQL_FIND_BY_ORDER_STATUS);
			paramsMap.put("orderStatus", orderStatus.toString());
		}
		
		if(null != fromUpdateTime && null != toUpdateTime){
			if(sql.length() > 0)
				sql.append(SQL_INTERSECT_STRING);
			sql.append(SQL_FIND_BY_PREFIX);
			sql.append(SQL_FIND_BY_UPDATE_TIME_SUFFIX_1);
			paramsMap.put("fromUpdateTime", fromUpdateTime);
			paramsMap.put("toUpdateTime", toUpdateTime);
		} else if(null != fromUpdateTime || null != toUpdateTime){
			if(sql.length() > 0)
				sql.append(SQL_INTERSECT_STRING);
			sql.append(SQL_FIND_BY_PREFIX);
			String tmpSql = MessageFormat.format(SQL_FIND_BY_UPDATE_TIME_SUFFIX_2, 
					null != fromUpdateTime? ">": "<");
			sql.append(tmpSql);
			paramsMap.put("updateTime", null != fromUpdateTime? fromUpdateTime: toUpdateTime);
		}
		
		if(null != fromCreateTime && null != toCreateTime){
			if(sql.length() > 0)
				sql.append(SQL_INTERSECT_STRING);
			sql.append(SQL_FIND_BY_PREFIX);
			sql.append(SQL_FIND_BY_CREATE_TIME_SUFFIX_1);
			paramsMap.put("fromCreateTime", fromCreateTime);
			paramsMap.put("toCreateTime", toCreateTime);
		} else if(null != fromCreateTime || null != toCreateTime){
			if(sql.length() > 0)
				sql.append(SQL_INTERSECT_STRING);
			sql.append(SQL_FIND_BY_PREFIX);
			String tmpSql = MessageFormat.format(SQL_FIND_BY_CREATE_TIME_SUFFIX_2, 
					null != fromCreateTime? ">": "<");
			sql.append(tmpSql);
			paramsMap.put("createTime", null != fromCreateTime? fromCreateTime: toCreateTime);
		}
		
		if(null != fromPrice && null != toPrice){
			if(sql.length() > 0)
				sql.append(SQL_INTERSECT_STRING);
			sql.append(SQL_FIND_BY_PREFIX);
			sql.append(SQL_FIND_BY_PRICE_SUFFIX_1);
			paramsMap.put("fromPrice", fromPrice);
			paramsMap.put("toPrice", toPrice);
		} else if(null != fromPrice || null != toPrice){
			if(sql.length() > 0)
				sql.append(SQL_INTERSECT_STRING);
			sql.append(SQL_FIND_BY_PREFIX);
			String tmpSql = MessageFormat.format(SQL_FIND_BY_PRICE_SUFFIX_2, 
					null != fromPrice? ">": "<");
			sql.append(tmpSql);
			paramsMap.put("price", null != fromPrice? fromPrice: toPrice);
		}
		
		if(sql.length() > 0){
			String finalSql = MessageFormat.format(SQL_FIND_BY_CRITERIA, sql);
			List<Long> ids = new ArrayList<Long>();
			List<Number> tmpIds = null;
			Query query = this.entityManager.createNativeQuery(finalSql);
			for(Map.Entry<String, Object> entry : paramsMap.entrySet()){
				query.setParameter(entry.getKey(), entry.getValue());
			}
			tmpIds = query.getResultList();
			for(Number tmpId : tmpIds){
				ids.add(tmpId.longValue());
			}
			orders = this.findByIds(ids.toArray(new Long[]{}));
		} else{
			orders = this.findAll();
		}
		return orders;
	}

	/* (non-Javadoc)
	 * @see idv.takeshi.software.productline.bookstore.domain.model.order.OrderRepository#findAll()
	 */
	@SuppressWarnings("unchecked")
	public Set<Order> findAll() {
		Set<Order> orders = null;
		Query query = this.entityManager.createNamedQuery("findAllOrders");
		orders = new LinkedHashSet<Order>(query.getResultList());
		return orders;
	}

	/* (non-Javadoc)
	 * @see idv.takeshi.software.productline.bookstore.domain.model.order.OrderRepository#findByIds(java.lang.Long[])
	 */
	@SuppressWarnings("unchecked")
	public Set<Order> findByIds(Long[] ids) {
		Set<Order> orders = null;
		String ql = null;
		if(ArrayUtils.isNotEmpty(ids)){
			StringBuffer idsString = new StringBuffer();
			int a = 0;
			for(Long id : ids){
				if(a == 0){
					idsString.append(id);
				} else{
					idsString.append(", " + id);
				}
				a++;
			}
			ql = MessageFormat.format(QL_FIND_BY_IDS, idsString.toString());
			Query query = this.entityManager.createQuery(ql);
			orders = new LinkedHashSet<Order>(query.getResultList());
		} else{
			orders = new LinkedHashSet<Order>();
		}
		return orders;
	}

}
